package main.java.jdbc11;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @Auther:Liu
 * @Testname:MyDbHandle
 * @Date:2025/3/12 9:33
 *
 *
 *
 * 数据库操作类
 */
public class MyDbHandle {
    static boolean flag = true;
     String url ;
    String user ;
     String password ;
     String driver;

    public MyDbHandle() {
        Properties pro = new Properties();
        //i/o流
        try {
            FileInputStream fileInputStream = new FileInputStream("src/jdbc11/jdbc.properties");
            pro.load(fileInputStream);
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            url = pro.getProperty("url");
            driver = pro.getProperty("driver");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public MyDbHandle(String url, String user, String password) {
        this.url = url;
        this.user = user;
        this.password = password;
    }

    public static void main(String[] args) throws Exception {
        MyDbHandle myDbHandle = new MyDbHandle();
        myDbHandle.findAll();
    }

    //连接
    public Connection Link() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //创建对象
    public int state(String sql, Connection conn) {
        int i = 0;
        Statement statement = null;
        try {
            statement = conn.createStatement();
            i = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //创建对象（预编译）
    public int state1(String sql, Connection conn) {
        int i = 0;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;

    }

    //查询(单个)
    public void findOne(int id) throws Exception {
        Connection conn = Link();
        //3.定义查询语句
        String sql = "select * from stu_student where stu_id='" + id + "'";
        //4.获取查询对象
        Statement statement = conn.createStatement();
        //5.实用对象执行数据
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int stu_id = resultSet.getInt("stu_id");
            String stu_name = resultSet.getString("stu_name");
            int stu_age = resultSet.getInt("stu_age");
            String stu_sex = resultSet.getString("stu_sex");
            int stu_phone = resultSet.getInt("stu_phone");
            String stu_address = resultSet.getString("stu_address");
            System.out.println(stu_id + "||" + stu_name + "||" + stu_age + "||" + stu_sex + "||" + stu_phone + "||" + stu_address);

        }
        //7.释放
        statement.close();
        conn.close();
    }

    //查询(多个)
    public void findAll() throws Exception {
        Connection conn = Link();
        //3.定义查询语句
        String sql = "select * from stu_student";
        //4.获取查询对象
        Statement statement = conn.createStatement();
        //5.实用对象执行数据
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int stu_id = resultSet.getInt("stu_id");
            String stu_name = resultSet.getString("stu_name");
            int stu_age = resultSet.getInt("stu_age");
            String stu_sex = resultSet.getString("stu_sex");
            int stu_phone = resultSet.getInt("stu_phone");
            String stu_address = resultSet.getString("stu_address");
            System.out.println(stu_id + "||" + stu_name + "||" + stu_age + "||" + stu_sex + "||" + stu_phone + "||" + stu_address);
        }
        //7.释放
        statement.close();
        conn.close();
    }

    //插入
    public void insert(String name2, int age2, String sex2, int phone2, String area2) throws Exception {
        Connection conn = Link();
        String sql = "insert into stu_student values(null,'" + name2 + "','" + age2 + "','" + sex2 + "','" + phone2 + "','" + area2 + "')";
        int i = state1(sql, conn);
        if (i != 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }
    }

    //更新
    public void update(int id, String name, int age, String sex, int phone, String area) throws Exception {
        Connection conn = Link();
        String sql = "update stu_student set stu_name='" + name + "',stu_age='" + age + "',stu_sex='" + sex + "',stu_phone='" + phone + "',stu_address='" + area + "' where stu_id='" + id + "'";
        int i = state(sql, conn);
        if (i != 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败，对象不存在");
        }
    }

    //删除
    public void delete(int id) throws Exception {
        Connection conn = Link();
        String sql = "delete from stu_student where stu_id='" + id + "'";
        int i = state(sql, conn);
        if (i != 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }

    //检验管理员(Statement)
    public boolean Search(String admire1, int password1) throws SQLException {
        Connection conn = Link();
        String sql = "select * from manage where admire='" + admire1 + "'and password='" + password1 + "'";
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        return resultSet.next();
    }
}
